import logging
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.exc import SQLAlchemyError
import psycopg2
from psycopg2 import OperationalError, ProgrammingError
import pymongo
import bson
from pymongo import MongoClient
from pymongo.errors import PyMongoError
import threading
import sqlite3

logging.basicConfig(level=logging.ERROR)

class Store:

    def __init__(self):
        
        self.create_bookstore_database()

        self.engine = create_engine('postgresql://postgres:wyd370685.@127.0.0.1:5432/bookstore')
        self.DBSession: type[Session] = None

        self.client = MongoClient('localhost', 27017)

        self.init_basic_data() # init

    # set up bookstore database in postgresql  
    def create_bookstore_database(self):
     conn = None
     try:
        conn = psycopg2.connect(
            dbname="postgres",
            user="postgres",
            password="wyd370685.", 
            host="localhost",
            port="5432"
        )
        conn.autocommit = True
        cursor = conn.cursor()

        cursor.execute("SELECT 1 FROM pg_database WHERE datname = 'bookstore';")
        if not cursor.fetchone():
            cursor.execute("CREATE DATABASE bookstore;")
            print("数据库 bookstore 创建成功")
        
        conn.close()

     except Exception as e:
            self._log_and_raise(f"创建数据库失败: {str(e)}", e)

    # all init steps
    def init_basic_data(self):
     try:
        
        mongo_conn = self.get_db_mongo()           # MongoDB数据迁移较为简单   
        mongo_collection = mongo_conn['book_details']  

        mongo_collection.create_index([("book_id", 1)], unique=True)

        self.migrate_data_from_sqlite_to_mongo(mongo_conn)   # 手动调用迁移数据至mongodb

        with self.get_db_conn() as postgres_conn:  # 使用with进行postgresql事务

            self.create_postgres_tables(postgres_conn)

            self.migrate_data_from_sqlite_to_postgre(postgres_conn)  # 手动调用迁移数据至postgresql

     except Exception as e:
            self._log_and_raise(f"初始化基础数据失败: {str(e)}", e)
    
    # set up tables
    def create_postgres_tables(self, conn):

        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS users (
                user_id TEXT PRIMARY KEY, 
                password TEXT NOT NULL, 
                balance INTEGER NOT NULL DEFAULT 0, 
                token TEXT, 
                terminal TEXT
            );
        """))

        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS user_store (
                user_id TEXT, 
                store_id TEXT PRIMARY KEY, 
                FOREIGN KEY (user_id) REFERENCES users(user_id)
            );
        """))

        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS book (
                id TEXT PRIMARY KEY,
                title TEXT NOT NULL,
                author TEXT,
                publisher TEXT,
                original_title TEXT,
                translator TEXT,
                pub_year TEXT,
                pages INTEGER,
                price INTEGER,
                currency_unit TEXT,
                binding TEXT,
                isbn TEXT,
                tags TEXT,
                search_vector TSVECTOR
            );
        """))

        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS store_book (
                store_id TEXT, 
                book_id TEXT, 
                stock_level INTEGER, 
                price INTEGER, 
                PRIMARY KEY (store_id, book_id),
                FOREIGN KEY (store_id) REFERENCES user_store(store_id),
                FOREIGN KEY (book_id) REFERENCES book(id)
            );
        """))

        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS orders (
                order_id TEXT PRIMARY KEY, 
                user_id TEXT, 
                store_id TEXT, 
                status INTEGER DEFAULT 1, 
                total_price INTEGER, 
                created_at INTEGER, 
                paid_at INTEGER, 
                shipped_at INTEGER, 
                received_at INTEGER, 
                FOREIGN KEY (user_id) REFERENCES users(user_id),
                FOREIGN KEY (store_id) REFERENCES user_store(store_id)
            );
        """))

        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS order_book (
                order_id TEXT, 
                book_id TEXT, 
                count INTEGER, 
                PRIMARY KEY (order_id, book_id),
                FOREIGN KEY (order_id) REFERENCES orders(order_id),
                FOREIGN KEY (book_id) REFERENCES book(id)
            );
        """))
        
        # 创建GIN索引
        conn.execute(text("""
        CREATE INDEX IF NOT EXISTS idx_book_search_vector 
        ON book 
        USING GIN (search_vector);
    """))
        
        # just override the same function
        conn.execute(text("""
        CREATE OR REPLACE FUNCTION update_book_search_vector()
        RETURNS TRIGGER AS $$
        BEGIN
            NEW.search_vector := 
                to_tsvector('english', COALESCE(NEW.title, '')) ||
                to_tsvector('english', COALESCE(NEW.author, '')) ||
                to_tsvector('english', COALESCE(NEW.tags, ''));
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;
    """))
        
        # 自动更新搜索向量的事件触发器
        conn.execute(text("""
        DO $$
        BEGIN
            IF NOT EXISTS (
                SELECT 1
                FROM information_schema.triggers
                WHERE trigger_name = 'update_book_search_vector_trigger'
                AND event_object_table = 'book'
            ) THEN
                CREATE TRIGGER update_book_search_vector_trigger
                BEFORE INSERT OR UPDATE ON book
                FOR EACH ROW EXECUTE FUNCTION update_book_search_vector();
            END IF;
        END $$;
    """))

        conn.commit()
    
    #migrate date from sqlite to postgre
    def migrate_data_from_sqlite_to_postgre(self, postgres_conn):
        try:
            sqlite_conn = sqlite3.connect('sqlite3:///../../fe/data/book.db') # 相对路径
            sqlite_cursor = sqlite_conn.cursor()
            sqlite_cursor.execute('SELECT * FROM book')

            for row in sqlite_cursor.fetchall():

                (book_id, title, author, publisher,
                 original_title, translator, pub_year,
                 pages, price, currency_unit, binding,
                 isbn, author_intro, book_intro,
                 content, tags, picture) = row

                postgres_conn.execute(text("""
                    INSERT INTO book (
                        id, title, author, publisher, original_title, 
                        translator, pub_year, pages, price, currency_unit, 
                        binding, isbn, tags
                    ) VALUES (
                        :id, :title, :author, :publisher, :original_title,
                        :translator, :pub_year, :pages, :price, :currency_unit,
                        :binding, :isbn, :tags
                )"""), {
                    "id": book_id,
                    "title": title,
                    "author": author,
                    "publisher": publisher,
                    "original_title": original_title,
                    "translator": translator,
                    "pub_year": pub_year,
                    "pages": pages,
                    "price": price,
                    "currency_unit": currency_unit,
                    "binding": binding,
                    "isbn": isbn,
                    "tags": tags
                })

            postgres_conn.commit() 
            sqlite_conn.close() 

        except sqlite3.Error as e:
            logging.error(f"SQLite 错误 in postgre migration: {str(e)}")
    
    #migrate date from sqlite to mongo
    def migrate_data_from_sqlite_to_mongo(self, mongo_db):
        try:
            sqlite_conn = sqlite3.connect('sqlite3:///../../fe/data/book.db') # 相对路径
            sqlite_cursor = sqlite_conn.cursor()
            sqlite_cursor.execute('SELECT * FROM book')
            
            mongo_col = mongo_db['book_details']

            for row in sqlite_cursor.fetchall():

                (book_id, title, author, publisher,
                 original_title, translator, pub_year,
                 pages, price, currency_unit, binding,
                 isbn, author_intro, book_intro,
                 content, tags, picture) = row

                mongo_doc = {
                    "book_id": book_id,
                    "author_intro": author_intro,
                    "book_intro": book_intro,
                    "content": content
                }
                if picture:
                    mongo_doc["pictures"] = bson.Binary(picture) 
                
                mongo_col.update_one(
                    {"book_id": book_id},
                    {"$set": mongo_doc},
                    upsert=True
                )

            sqlite_conn.close() 

        except sqlite3.Error as e:
            logging.error(f"SQLite 错误 in mongo_migration: {str(e)}")
        
    # postgresql connection
    def get_db_conn(self):
        if not self.DBSession:
            self.DBSession = sessionmaker(bind=self.engine, class_=Session)
        self.conn = self.DBSession()
        return self.conn
    
    # mongodb connection
    def get_db_mongo(self):
        return self.client["bookstore"]


init_completed_event = threading.Event()

database_instance: Store = None

def init_database():
    global database_instance
    database_instance = Store()

def get_db_conn():  # postgresql connection for outer scope
    global database_instance
    return database_instance.get_db_conn()

def get_db_mongo(): # mongodb connection for outer scope
    global database_instance
    return database_instance.get_db_mongo()